package ddz.utils;

import ch.qos.logback.classic.Logger;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 简单Excel表哥读取工具，一般用于将策划提供的配置表进行转换<br>
 * 此为POI用户模式实现，如需读取大文件需用事件模式读取
 *
 * @author zhoukai
 */
public class ExcelReader {

    private final Logger logger = (Logger) LoggerFactory.getLogger(ExcelReader.class);

    //excel工作簿
    private Workbook wb;

    /**
     * 构造方法
     *
     * @param is 文件输入流
     */
    public ExcelReader(InputStream is) {
        try {
            wb = WorkbookFactory.create(is);
        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
        }
    }

    /**
     * 构造方法
     *
     * @param file 文件
     * @throws FileNotFoundException
     */
    public ExcelReader(File file) throws FileNotFoundException {
        this(new FileInputStream(file));
    }

    /**
     * 构造方法
     *
     * @param path 文件路径
     * @throws FileNotFoundException
     */
    public ExcelReader(String path) throws FileNotFoundException {
        this(new FileInputStream(path));
    }

    /**
     * 取Excel所有数据，包含header
     *
     * @param sheetIndex excel sheet索引
     * @return 所有行数据
     */
    public List<String[]> getSheetData(int sheetIndex) {
        int columnNum = 0;
        Sheet sheet = getSheet(sheetIndex);
        if (sheet.getRow(0) != null) {
            Row row = sheet.getRow(0);
            columnNum = row.getLastCellNum() - row.getFirstCellNum();
            for (int i = 0; i < columnNum; i++) {
                Cell cell = row.getCell(i);
                String str = cell.getStringCellValue();
                if ("".equals(str)) {
                    //第一行当遇到第一个空
                    columnNum = i;
                    break;
                }
            }
        }
        List<String[]> list = null;
        if (columnNum > 0) {
            list = new ArrayList<>();
            for (Row row : sheet) {
                String[] singleRow = getRowData(row, columnNum);
                if ("".equals(singleRow[0])) {
                    continue;
                }//如果第一行为空，跳过
                list.add(singleRow);
            }
        }
        return list;
    }

    /**
     * 获取工作簿中的excel表格文档
     *
     * @param index 表格文档索引
     * @return 表格文档
     */
    public Sheet getSheet(int index) {
        return wb.getSheetAt(index);
    }

    /**
     * 获取行数据
     *
     * @param row    行
     * @param colNum 列数目
     * @return 行数据
     */
    private String[] getRowData(Row row, int colNum) {
        if (colNum <= 0) {
            colNum = row.getLastCellNum() - row.getFirstCellNum();
        }
        String[] singleRow = new String[colNum];
        FormulaEvaluator evaluator = this.wb.getCreationHelper().createFormulaEvaluator();
        DataFormatter formater = new DataFormatter();
        for (int i = 0, n = 0; i < colNum; i++, n++) {
            Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            String cellValue = formater.formatCellValue(cell, evaluator);
            singleRow[n] = cellValue;
        }
        return singleRow;
    }

    /**
     * 返回Excel最大行index值，实际行数要加1
     *
     * @param sheetIndex excel sheet索引
     * @return 最大行索引
     */
    public int getRowNum(int sheetIndex) {
        Sheet sheet = getSheet(sheetIndex);
        return this.getRowNum(sheet);
    }

    /**
     * 返回Excel最大行index值，实际行数要加1
     *
     * @param sheet
     * @return
     */
    public int getRowNum(Sheet sheet) {
        return sheet.getLastRowNum();
    }

    /**
     * 返回数据的列数
     *
     * @param sheetIndex
     * @return
     */
    public int getColNum(int sheetIndex) {
        Sheet sheet = getSheet(sheetIndex);
        return getColNum(sheet);
    }

    /**
     * 返回数据的列数
     *
     * @param sheet
     * @return
     */
    public int getColNum(Sheet sheet) {
        Row row = sheet.getRow(0);
        if (row != null && row.getLastCellNum() > 0) {
            return row.getLastCellNum();
        }
        return 0;
    }

    /**
     * 获取某一行数据
     *
     * @param sheetIndex
     * @param rowIndex   计数从0开始，rowIndex为0代表header行
     * @return
     */
    public String[] getRowData(int sheetIndex, int rowIndex) {
        Sheet sheet = getSheet(sheetIndex);
        int colNum = getColNum(sheet);
        if (rowIndex > colNum) {
            return null;
        }
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            return null;
        }
        return this.getRowData(row, colNum);
    }

    /**
     * 获取某一列数据
     *
     * @param sheetIndex
     * @param colIndex
     * @return
     */
    public String[] getColData(int sheetIndex, int colIndex) {
        String[] dataArray = null;
        if (colIndex > this.getColNum(sheetIndex)) {
            return dataArray;
        } else {
            List<String[]> dataList = getSheetData(sheetIndex);
            if (dataList != null && dataList.size() > 0) {
                dataArray = new String[this.getRowNum(sheetIndex) + 1];
                int index = 0;
                for (String[] rowData : dataList) {
                    if (rowData != null) {
                        dataArray[index] = rowData[colIndex];
                        index++;
                    }
                }
            }
        }
        return dataArray;
    }

    /**
     * 将文件转换为以制表符分隔单元格的文本
     *
     * @return 文本内容
     */
    public StringBuilder getTextContent() {
        List<String[]> list = this.getSheetData(0);
        int size = list.size();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < size; i++) {
            String[] vs = list.get(i);
            if ("".equals(vs[0])) {
                continue;
            }
            StringBuilder line = new StringBuilder();
            for (String v : vs) {
                if (i == 1) {
                    v = v.replaceAll(" ", "");
                }
                line.append(v).append('\t');
            }
            line.deleteCharAt(line.length() - 1);
            sb.append(line).append('\n');
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb;
    }

    /**
     * 释放资源
     */
    public void dispose() {
        if (this.wb != null) {
            try {
                wb.close();
            } catch (IOException ex) {
                logger.error(ex.getLocalizedMessage());
            }
        }
    }

}
